package leave.dataaccess.mysql;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

import leave.dataaccess.Authenticate;
import leave.dataaccess.EmployeeLeaveDAO;
import leave.exception.DataAccessException;
import leave.model.CompensationLeaveLedger;
import leave.model.Employee;
import leave.model.Leave;
import leave.model.impl.mysql.*;

public class EmployeeLeaveDAOImpl implements EmployeeLeaveDAO, Authenticate {

	private Connection factoryConnection;
	
	private String queryCommand = "select emp.name, emp.password, r.role_name, emp.reports_to, emp.rank" +
	  							  "from employee emp, role r" +
	  							  "where emp.employeeID = ?" +
	  							  "and emp.role_id = role.role_id;";
	private final int emp_name = 1;
	private final int emp_password = 2;
	private final int emp_role = 3;
	private final int emp_reports_to = 4;
	private final int emp_rank = 5;
	
	
	
	public EmployeeLeaveDAOImpl(Connection factoryConnection) {
		super();
		this.factoryConnection = factoryConnection;
	}
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.EmployeeLeaveDAO#getEmployee(int)
	 * Gets the employee using employeeID
	 * Returns Employee instance if the user is employee and
	 * Manager if user is manager.
	 * 
	 * Note that Manager instance inherits from Employee instance.
	 */
	// TODO This code needs to be edited.
	@Override
	public Employee getEmployee(int employeeID) throws DataAccessException {
		HashMap<String, Employee> empSelect = new HashMap<String, Employee>();
		Employee emp = null;
		/*
		String query = "select emp1.*,emp2.* from employee emp1, employee emp2 where emp1.employee_id= "
				+ employeeID + " and emp1.reports_to=emp2.employee_id";
		*/
		
		PreparedStatement pst = null;
		
		empSelect.put("staff", new EmployeeImpl());
		empSelect.put("manager", new ManagerImpl());
		
		try {
			pst = factoryConnection.prepareStatement(queryCommand);
			pst.setInt(1, employeeID);
			ResultSet rs = pst.executeQuery();
			emp = empSelect.get(rs.getString(emp_role));
			createEmployee(employeeID, emp, rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException(
						"Unable to carry out transaction.", e);
			}
		}
		return emp;
	}

	private void createEmployee(int employeeID, Employee emp, ResultSet rs)
	throws SQLException, DataAccessException {
		
		if (rs != null) {
			rs.next();
			
			emp.setEmployeeID(employeeID);
			emp.setName(rs.getString(emp_name));
			emp.setPassword(rs.getString(emp_password));
			emp.setRole(rs.getString(emp_role));
			emp.setRank(rs.getString(emp_rank));
			
			PreparedStatement psts = factoryConnection.prepareStatement(queryCommand);
			psts.setInt(1, rs.getInt(emp_reports_to));
			ResultSet rss = psts.executeQuery();
			Employee superior = null;
			if (rss != null){
				rss.next();
				superior = new EmployeeImpl();
				superior.setEmployeeID(rs.getInt(emp_reports_to));
				superior.setName(rs.getString(emp_name));
				superior.setPassword(rs.getString(emp_password));
				superior.setRole(rs.getString(emp_role));
				superior.setRank(rs.getString(emp_rank));
			}
			psts.close();
			emp.setReportsTo(superior);
		}

		ArrayList<Integer> leavesTaken = new ArrayList<Integer>();

		Leave[] leaves = new LeaveDAOImpl(factoryConnection)
		.getLeaveTransactions(emp);
		for (int i = 0; i < leaves.length; i++) {
			leavesTaken.add(leaves[i].getLeaveID());
		}

		emp.setLeaveTakenID(leavesTaken);

		ArrayList<Integer> compensationsClaimed = new ArrayList<Integer>();

		CompensationLeaveLedger[] cll = new CompensationLeaveLedgerDAOImpl(
				factoryConnection).getAllLedger(emp);

		for (int i = 0; i < cll.length; i++) {
			compensationsClaimed.add(cll[i].getTransactionID());
		}

		emp.setCompensationsClaimed(compensationsClaimed);
		emp.setCompensatableDays(new EmployeeCompensationLeaveDAOImpl(
				factoryConnection).getCompensationDays(emp.getEmployeeID())); 
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.EmployeeLeaveDAO#getAllEmployees()
	 * Gets all the employees
	 * Returns Employee instance (Not managers)
	 */
	@Override
	public Employee[] getAllEmployees() throws DataAccessException {
		
		Statement st = null;
		Employee[] employees = null;
		//String query ="select emp1.*,emp2.* from employee emp1, employee emp2 where emp1.reports_to = emp2.employee_id";

		String queryAllCommand = "select emp.employeeID, emp.name, emp.password, r.role_name, emp.reports_to, emp.rank" +
		  						 "from employee emp, role r" +
		  						 "where emp.role_id = role.role_id;";
		
		try {
			st = factoryConnection.createStatement();
			ResultSet rs = st.executeQuery(queryAllCommand);		
			employees =  parseEmployee(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		}
		finally{
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
		return employees;
	}
	
	private Employee[] parseEmployee(ResultSet rs) throws SQLException, DataAccessException
	{
		final int id = 1;
		final int name = 2;
		final int password = 3;
		final int role = 4;
		final int reports_to = 5;
		final int rank = 6;
		
		if(rs != null)
		{
			Employee[] employees = new Employee[rs.getRow()];
			for(int count = 0; rs.next(); count++)
			{
				employees[count] = new EmployeeImpl();
				employees[count].setEmployeeID(rs.getInt(id));
				employees[count].setName(rs.getString(name));
				employees[count].setPassword(rs.getString(password));
				employees[count].setRole(rs.getString(role));
				employees[count].setRank(rs.getString(rank));
				
				PreparedStatement psts = factoryConnection.prepareStatement(queryCommand);
				psts.setInt(1, rs.getInt(reports_to));
				ResultSet rss = psts.executeQuery();
				Employee superior = null;
				if (rss != null){
					rss.next();
					superior = new EmployeeImpl();
					superior.setEmployeeID(rs.getInt(emp_reports_to));
					superior.setName(rs.getString(emp_name));
					superior.setPassword(rs.getString(emp_password));
					superior.setRole(rs.getString(emp_role));
					superior.setRank(rs.getString(emp_rank));
				}
				psts.close();
				employees[count].setReportsTo(superior);	
			}
			return employees;
			                               
		}
		else
			return null;
	}
	
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.EmployeeLeaveDAO#insertEmployee(leave.model.Employee)
	 * Adds employee into the database.
	 */
	@Override
	public void insertEmployee(Employee employee) throws DataAccessException {
		PreparedStatement pst = null;
		
		String insertCommand = "insert into employee" +
		   					   "(name, password, role_id, reports_to, email, rank_id)" +
		   					   "values(?, ?, ?, ?, ?, ?)";
		try {
			Integer role_id = new Integer(0);
			Integer rank_id = new Integer(0);
			this.getRoleAndRank(role_id, rank_id, employee);
			
			/*
			st = factoryConnection.createStatement();
			StringBuffer query = new StringBuffer();
			query.append("insert into employee(name,password,role_id,reports_to,email,rank_id) values('")
			.append(employee.getName()).append("' , '").append(employee.getPassword()).append("' , ").append(employee.getRole()).append(" , ")
			.append(employee.getReportsTo().getEmployeeID()).append(" , '").append(employee.getEmail()).append("' , ")
			.append(employee.getRank()).append(")");
			*/
			pst = factoryConnection.prepareStatement(insertCommand);			
			
			pst.setString(1, employee.getName());
			pst.setString(2, employee.getPassword()); 
			pst.setInt(3, role_id);
			pst.setInt(4, employee.getReportsTo().getEmployeeID());
			pst.setString(5, employee.getEmail());
			pst.setInt(6, rank_id);
			pst.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		}
		finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}	
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.EmployeeLeaveDAO#updateEmployee(leave.model.Employee)
	 * Updates employee into the database.
	 */
	@Override
	public void updateEmployee(Employee employee) throws DataAccessException {
		PreparedStatement pst = null;
		String updateCommand = "update employee" +
							   "set name = ?," +
							   "password = ?," +
							   "role_id = ?," +
							   "reports_to = ?," +
							   "email = ?," +
							   "rank_id = ?" +
							   "where employeeID = ?;";
		
		try {
			
			Integer role_id = new Integer(0);
			Integer rank_id = new Integer(0);
			this.getRoleAndRank(role_id, rank_id, employee);
			
			/*
				st = factoryConnection.createStatement();
			
			StringBuffer query = new StringBuffer();
			query.append("update employee set name='").append(employee.getName()).append("' , password='").append(employee.getPassword())
			.append("' , role_id=").append(employee.getRole()).append(" , reports_to=").append(employee.getReportsTo().getEmployeeID())
			.append(", email='").append(employee.getEmail()).append("' , rank_id=").append(employee.getRank()).append(" where employee_id=")
			.append(employee.getEmployeeID());
			*/
			pst = factoryConnection.prepareStatement(updateCommand);
			pst.setString(1, employee.getName());
			pst.setString(2, employee.getPassword());
			pst.setInt(3, role_id);
			pst.setInt(4, employee.getReportsTo().getEmployeeID());
			pst.setString(5, employee.getEmail());
			pst.setInt(6, rank_id);
			pst.setInt(7, employee.getEmployeeID());
			
			pst.executeUpdate();
			//st.executeQuery(query.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		}
		finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}

	private void getRoleAndRank(Integer role, Integer rank, Employee employee)
			throws SQLException {
		String roleCommand = "select role_id" + "from role"
				+ "where role_name = ?";
		String rankCommand = "select rank_id" + "from rank"
				+ "where rank_name = ?";

		PreparedStatement pst = factoryConnection.prepareStatement(roleCommand);
		pst.setString(1, employee.getRole());
		ResultSet rs = pst.executeQuery();
		rs.next();
		role = rs.getInt("role_id");
		pst.close();

		pst = factoryConnection.prepareStatement(rankCommand);
		pst.setString(1, employee.getRank());
		rs = pst.executeQuery();
		rs.next();
		rank = rs.getInt("rank_id");
		pst.close();
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.EmployeeLeaveDAO#deleteEmployee(leave.model.Employee)
	 * Deletes employee from the database.
	 */
	@Override
	public void deleteEmployee(Employee employee) throws DataAccessException {
		Statement st = null;
		try {
			st = factoryConnection.createStatement();
			
			String query = "delete from employee where employee_id = " + employee.getEmployeeID();
			st.executeUpdate(query);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		}
		finally{
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to carry out transaction.", e);
			}
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.Authenticate#authenticate(int, java.lang.String)
	 * Check if the user has enter correct username and password.
	 */
	@Override
	public boolean authenticate(int userName, String password) throws DataAccessException {
		
		Statement st = null;
		boolean result;
		try {
			st = factoryConnection.createStatement();
			
			String query = "select * from employee where name=" + userName + " and password=" + password;
			ResultSet rs = st.executeQuery(query);
			
			result = (rs != null) ? true : false;
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to authenticate user.", e);
		}
		finally{
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new DataAccessException("Unable to authenticate user.", e);
			}
		}
		return result;
	}

	

}
